package com.hqhsc.module.common.service;

import com.hqhsc.module.common.domain.OffLineChannelElement;
import com.hqhsc.module.manage.activity.domain.Activity;
import com.hqhsc.module.manage.activity.domain.ActivityChannelDetail;
import com.hqhsc.module.manage.activityuser.domain.ActivityUser;
import com.hqhsc.module.manage.channel.domain.Channel;
import com.swetake.util.Qrcode;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;
import java.util.List;


@Service
public class FileService {

	static Logger logger = LoggerFactory.getLogger(FileService.class);

    @Autowired
    private Properties appProps;

    private static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    private static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";


    public Map<String , String> uploadFile(CommonsMultipartFile file_upload , String save_path , String file_sys_name) {
        File f = new File(save_path);
        if(!f.exists()) {
            f.mkdir();
        }
        try{
            if(!file_upload.isEmpty()) {
//                long upload_size = file_upload.getFileItem().getSize();
                String name = file_upload.getFileItem().getName();
                String ext_name = "";
                String origin_name = "";
                if(name.lastIndexOf(".") > 0) {
                    name = name.substring(name.lastIndexOf("\\") + 1);
                    ext_name = name.substring(name.lastIndexOf("."));
                    origin_name = name.substring(0 , name.lastIndexOf("."));
                }
                String file_path = save_path + "/" + file_sys_name + ext_name;
                File file = new File(file_path);
                file_upload.getFileItem().write(file);

                Map<String , String> map = new HashMap<String, String>();
                map.put("origin_name" , origin_name);
                map.put("file_path" , file_path);
                map.put("ext_name" , ext_name);

                return map;
            }
        } catch (Exception e) {
            logger.error(e.getMessage() , e);
        }
        return null;
    }

    public List<OffLineChannelElement> analysisExcel(String path) {
        String postfix = path.substring(path.lastIndexOf(".") + 1 , path.length());
        if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
            return this.analysisXls(path);
        } else if(OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
            return this.analysisXlsx(path);
        }
        return null;
    }

    private String getXlsValue(HSSFCell hssfCell) {
        int cell_type = hssfCell.getCellType();
        if(cell_type == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if(cell_type == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    private String getXlsxValue(XSSFCell xssfCell) {
        if(xssfCell == null) {
            return "";
        }
        int cell_type = xssfCell.getCellType();
        if(cell_type == xssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if(cell_type == xssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    public List<OffLineChannelElement> analysisXls(String path) {
        List<OffLineChannelElement> list = new ArrayList<OffLineChannelElement>();
        try{
            InputStream is = new FileInputStream(path);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
            for(int num_sheet = 0 ; num_sheet < hssfWorkbook.getNumberOfSheets() ; num_sheet++) {
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(num_sheet);
                if(hssfSheet == null) {
                    continue;
                }
                for(int row_num = 1 ; row_num < hssfSheet.getLastRowNum() ; row_num++) {
                    HSSFRow hssfRow = hssfSheet.getRow(row_num);
                    if(hssfRow != null) {
                        OffLineChannelElement oe = new OffLineChannelElement();
                        HSSFCell user_name = hssfRow.getCell(0);
                        HSSFCell dn = hssfRow.getCell(1);
                        HSSFCell sex = hssfRow.getCell(2);
                        HSSFCell is_buy = hssfRow.getCell(3);
                        HSSFCell nobuy_reason = hssfRow.getCell(4);
                        HSSFCell goods_name = hssfRow.getCell(5);
                        HSSFCell pay_amount = hssfRow.getCell(6);
                        HSSFCell is_discount = hssfRow.getCell(7);
                        HSSFCell channel_name = hssfRow.getCell(8);

                        oe.setName(getXlsValue(user_name));
                        oe.setDn(getXlsValue(dn));
                        oe.setSex(getXlsValue(sex));
                        oe.setIs_buy(getXlsValue(is_buy));
                        oe.setNobuy_reason(getXlsValue(nobuy_reason));
                        oe.setBuy_googds(getXlsValue(goods_name));
                        oe.setPay_amount(getXlsValue(pay_amount));
                        oe.setIs_discount(getXlsValue(is_discount));
                        oe.setChannel_name(getXlsValue(channel_name));

                        list.add(oe);
                    }
                }
            }

        } catch (Exception e) {
            logger.error(e.getMessage() , e);
        }
        return list;
    }

    public List<OffLineChannelElement> analysisXlsx(String path) {
        List<OffLineChannelElement> list = new ArrayList<OffLineChannelElement>();
        try{
            InputStream is = new FileInputStream(path);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

            DecimalFormat df = new DecimalFormat("#");

            for(int num_sheet = 0 ; num_sheet < xssfWorkbook.getNumberOfSheets() ; num_sheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(num_sheet);
                if(xssfSheet == null) {
                    continue;
                }
                for(int row_num = 1 ; row_num < xssfSheet.getLastRowNum() ; row_num++) {
                    XSSFRow xssfRow = xssfSheet.getRow(row_num);
                    if(xssfRow != null) {
                        OffLineChannelElement oe = new OffLineChannelElement();
                        XSSFCell user_name = xssfRow.getCell(0);
                        XSSFCell dn = xssfRow.getCell(1);
                        XSSFCell sex = xssfRow.getCell(2);
                        XSSFCell is_buy = xssfRow.getCell(3);
                        XSSFCell nobuy_reason = xssfRow.getCell(4);
                        XSSFCell goods_name = xssfRow.getCell(5);
                        XSSFCell pay_amount = xssfRow.getCell(6);
                        XSSFCell is_discount = xssfRow.getCell(7);
                        XSSFCell channel_name = xssfRow.getCell(8);

                        oe.setName(getXlsxValue(user_name));
                        oe.setDn(getXlsxValue(dn));
                        oe.setSex(getXlsxValue(sex));
                        oe.setIs_buy(getXlsxValue(is_buy));
                        oe.setNobuy_reason(getXlsxValue(nobuy_reason));
                        oe.setBuy_googds(getXlsxValue(goods_name));
                        oe.setPay_amount(getXlsxValue(pay_amount));
                        oe.setIs_discount(getXlsxValue(is_discount));
                        oe.setChannel_name(getXlsxValue(channel_name));

                        list.add(oe);
                    }
                }
            }

        } catch (Exception e) {
            logger.error(e.getMessage() , e);
        }
        return list;
    }


    public void writeChannelExcel(List<Channel> channel_list , String export_path) {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("渠道详情");
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell(0);
        cell.setCellValue("渠道名称");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("网址");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("渠道类型");
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue("推广位置");
        cell.setCellStyle(style);
        cell = row.createCell(4);
        cell.setCellValue("推广周期");
        cell.setCellStyle(style);
        cell = row.createCell(5);
        cell.setCellValue("推广内容");
        cell.setCellStyle(style);
        cell = row.createCell(6);
        cell.setCellValue("推广成本");
        cell.setCellStyle(style);
        cell = row.createCell(7);
        cell.setCellValue("pv");
        cell.setCellStyle(style);
        cell = row.createCell(8);
        cell.setCellValue("uv");
        cell.setCellStyle(style);
        cell = row.createCell(9);
        cell.setCellValue("点击量");
        cell.setCellStyle(style);
        cell = row.createCell(10);
        cell.setCellValue("报名数");
        cell.setCellStyle(style);
        cell = row.createCell(11);
        cell.setCellValue("人均点击成本");
        cell.setCellStyle(style);
        cell = row.createCell(12);
        cell.setCellValue("人均报名成本");
        cell.setCellStyle(style);
        cell = row.createCell(13);
        cell.setCellValue("投放活动数");
        cell.setCellStyle(style);
        cell = row.createCell(14);
        cell.setCellValue("投放活动名称");
        cell.setCellStyle(style);

        for(int i = 0 ; i < channel_list.size() ; i++) {
            row = sheet.createRow(i+1);
            Channel channel = channel_list.get(i);
            row.createCell(0).setCellValue(channel.getName());
            row.createCell(1).setCellValue(channel.getWebsite_url());
            row.createCell(2).setCellValue(channel.getChannel_type());
            row.createCell(3).setCellValue(channel.getSpread_position());
            row.createCell(4).setCellValue(channel.getSpread_cycle());
            row.createCell(5).setCellValue(channel.getSpread_content());
            row.createCell(6).setCellValue(channel.getSpread_cost());
            row.createCell(7).setCellValue(channel.getPv());
            row.createCell(8).setCellValue(channel.getUv());
            row.createCell(9).setCellValue(channel.getPoint_count());
            row.createCell(10).setCellValue(channel.getSignup_count());
            if(channel.getPv() > 0) {
                row.createCell(11).setCellValue(Double.valueOf(channel.getSpread_cost()) / channel.getPv());
            } else {
                row.createCell(11).setCellValue(Double.valueOf(channel.getSpread_cost()));
            }

            if(channel.getSignup_count() > 0) {
                row.createCell(12).setCellValue(Double.valueOf(channel.getSpread_cost()) / channel.getSignup_count());
            } else {
                row.createCell(12).setCellValue(Double.valueOf(channel.getSpread_cost()));
            }
//            row.createCell(11).setCellValue(channel.getPoint_cost_avg());
//            row.createCell(12).setCellValue(channel.getSignup_cost_avg());
            row.createCell(13).setCellValue(channel.getActivity_num());
            row.createCell(14).setCellValue(channel.getActivity_names());
        }

        try{
            FileOutputStream out = new FileOutputStream(export_path);
            wb.write(out);
            out.close();
        } catch (Exception e) {
            logger.error(e.getMessage() , e);
        }
    }

    public void writeActivityOnlineUserExcel(List<ActivityUser> activity_user_list , String export_path) {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("线上报名用户详情");
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("报名时间");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("姓名");
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue("来源活动");
        cell.setCellStyle(style);
        cell = row.createCell(4);
        cell.setCellValue("来源渠道");
        cell.setCellStyle(style);
        cell = row.createCell(5);
        cell.setCellValue("联系电话");
        cell.setCellStyle(style);
        cell = row.createCell(6);
        cell.setCellValue("身份证号码");
        cell.setCellStyle(style);
        cell = row.createCell(7);
        cell.setCellValue("家庭住址");
        cell.setCellStyle(style);
        cell = row.createCell(8);
        cell.setCellValue("结婚预算");
        cell.setCellStyle(style);
        cell = row.createCell(9);
        cell.setCellValue("预定婚期");
        cell.setCellStyle(style);
        cell = row.createCell(10);
        cell.setCellValue("筹婚需求");
        cell.setCellStyle(style);
        cell = row.createCell(11);
        cell.setCellValue("是否签到");
        cell.setCellStyle(style);


        for(int i = 0 ; i < activity_user_list.size() ; i++) {
            row = sheet.createRow(i+1);
            ActivityUser activityUser = activity_user_list.get(i);
            row.createCell(0).setCellValue(activityUser.getId());
            row.createCell(1).setCellValue(activityUser.getCreate_at());
            row.createCell(2).setCellValue(activityUser.getName());
            row.createCell(3).setCellValue(activityUser.getActivity_name());
            row.createCell(4).setCellValue(activityUser.getChannel_name());
            row.createCell(5).setCellValue(activityUser.getDn());
            row.createCell(6).setCellValue(activityUser.getId_card());
            row.createCell(7).setCellValue(activityUser.getAddress());
            row.createCell(8).setCellValue(activityUser.getBudget());
            row.createCell(9).setCellValue(activityUser.getWedding_date());
            row.createCell(10).setCellValue(activityUser.getMarry_require());
            row.createCell(11).setCellValue(activityUser.getIs_sign_str());
        }

        try{
            FileOutputStream out = new FileOutputStream(export_path);
            wb.write(out);
            out.close();
        } catch (Exception e) {
            logger.error(e.getMessage() , e);
        }
    }

    public void writeActivityExcel(List<Activity> activity_list , String export_path) {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("活动详情");
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell(0);
        cell.setCellValue("活动名称");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("活动类型");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("活动开始时间");
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue("活动结束时间");
        cell.setCellStyle(style);
        cell = row.createCell(4);
        cell.setCellValue("活动创建时间");
        cell.setCellStyle(style);
        cell = row.createCell(5);
        cell.setCellValue("活动介绍");
        cell.setCellStyle(style);
        cell = row.createCell(6);
        cell.setCellValue("活动状态");
        cell.setCellStyle(style);
        cell = row.createCell(7);
        cell.setCellValue("PV");
        cell.setCellStyle(style);
        cell = row.createCell(8);
        cell.setCellValue("UV");
        cell.setCellStyle(style);
        cell = row.createCell(9);
        cell.setCellValue("点击量");
        cell.setCellStyle(style);
        cell = row.createCell(10);
        cell.setCellValue("报名数");
        cell.setCellStyle(style);
        cell = row.createCell(11);
        cell.setCellValue("活动URL地址");
        cell.setCellStyle(style);
        cell = row.createCell(12);
        cell.setCellValue("渠道名称");
        cell.setCellStyle(style);
        cell = row.createCell(13);
        cell.setCellValue("渠道平均点击成本");
        cell.setCellStyle(style);
        cell = row.createCell(14);
        cell.setCellValue("渠道平均报名成本");
        cell.setCellStyle(style);

        int row_num = 0;
        for(int i = 0 ; i < activity_list.size() ; i++) {
            Activity activity = activity_list.get(i);

            if(activity.getActivity_channel_detail_list().size() > 0) {
                for(int j = 0 ; j < activity.getActivity_channel_detail_list().size() ; j++) {
                    ActivityChannelDetail activityChannelDetail = activity.getActivity_channel_detail_list().get(j);
                    row_num = row_num + 1;
                    row = sheet.createRow(row_num);

                    row.createCell(0).setCellValue(activity.getName());
                    //TODO 需要换成获取枚举表中的活动类型
                    row.createCell(1).setCellValue("报名");
                    row.createCell(2).setCellValue(activity.getBegin_time());
                    row.createCell(3).setCellValue(activity.getEnd_time());
                    row.createCell(4).setCellValue(activity.getCreate_at());
                    row.createCell(5).setCellValue(activity.getDescription());
                    row.createCell(6).setCellValue((activity.getStatus() == 1) ? "上线":"下线");
                    row.createCell(7).setCellValue(activity.getPv());
                    row.createCell(8).setCellValue(activity.getUv());
                    row.createCell(9).setCellValue(activity.getPoint_count());
                    row.createCell(10).setCellValue(activity.getSignup_count());

                    String url = appProps.get("base.url") + "hqhsc/activity/signup/normal"+ "?aid=" + activity.getId() + "&cid=" + activityChannelDetail.getChannel_id();
                    row.createCell(11).setCellValue(url);
                    row.createCell(12).setCellValue(activityChannelDetail.getChannel_name());

                    Double point_cost_avg = Double.valueOf(activityChannelDetail.getSpread_cost());
                    if(activityChannelDetail.getPoint_count() > 0) {
                        point_cost_avg = Double.valueOf(activityChannelDetail.getSpread_cost()) / activityChannelDetail.getPoint_count();
                    }
                    row.createCell(13).setCellValue(point_cost_avg);

                    Double signup_cost_avg = Double.valueOf(activityChannelDetail.getSpread_cost());
                    if(activityChannelDetail.getSignup_count() > 0) {
                        signup_cost_avg = Double.valueOf(activityChannelDetail.getSpread_cost()) / activityChannelDetail.getSignup_count();
                    }
                    row.createCell(14).setCellValue(signup_cost_avg);
                }
            } else {
                row_num = row_num + 1;
                row = sheet.createRow(row_num);

                row.createCell(0).setCellValue(activity.getName());
                //TODO 需要换成获取枚举表中的活动类型
                row.createCell(1).setCellValue("报名");
                row.createCell(2).setCellValue(activity.getBegin_time());
                row.createCell(3).setCellValue(activity.getEnd_time());
                row.createCell(4).setCellValue(activity.getCreate_at());
                row.createCell(5).setCellValue(activity.getDescription());
                row.createCell(6).setCellValue((activity.getStatus() == 1) ? "上线":"下线");
                row.createCell(7).setCellValue(activity.getPv());
                row.createCell(8).setCellValue(activity.getUv());
                row.createCell(9).setCellValue(activity.getPoint_count());
                row.createCell(10).setCellValue(activity.getSignup_count());

                row.createCell(11).setCellValue("");
                row.createCell(12).setCellValue("");
                row.createCell(13).setCellValue(0);
                row.createCell(14).setCellValue(0);
            }
        }

        try{
            FileOutputStream out = new FileOutputStream(export_path);
            wb.write(out);
            out.close();
        } catch (Exception e) {
            logger.error(e.getMessage() , e);
        }
    }

    public void exportFile(HttpServletResponse response , File file , boolean isDel) throws IOException{
        OutputStream out;
        InputStream in;
        String file_name = URLEncoder.encode(file.getName() , "UTF-8");
        response.setContentType("application/force-download");
        response.setHeader("Location" , file_name);
        response.setHeader("Content-Disposition" , "attachment;filename=" + file_name);

        out = response.getOutputStream();
        in = new FileInputStream(file.getPath());

        byte[] buffer = new byte[1024];
        int i = -1;
        while ((i=in.read(buffer)) != -1) {
            out.write(buffer , 0 , i);
        }
        out.flush();;
        in.close();;
        out.close();
        if(isDel) {
            file.delete();
        }
    }
}
